<?php
/*
 * @Date: 2024-03-20 15:19:03
 * @LastEditors: 搬铁的码农 dong2406@126.com
 * @LastEditTime: 2024-04-27 09:08:18
 * 
 * 数据库备份类
 */

namespace Ldy\Lib;


use think\facade\Db;

class DataBackup
{

    /**
     * 数据库配置标识
     *
     * @var [type]
     */
    protected static $dbConnectName = null;

    /**
     * 要备份的数据表
     *
     * @var array
     */
    protected $tables = []; //['qs_lz_archives'];

    /**
     * 格式后缀
     *
     * @var string
     */
    protected $ext = 'sql';

    /**
     * 数据库操作对象
     *
     * @var [type]
     */
    protected $Db = null;


    /**
     * 分卷数
     *
     * @var integer
     */
    protected $roll = 0;

    /**
     * 默认结构和数据，为false转储为结构
     * @var bool
     */
    protected $withData = true;

    /**
     * true 删除存在的表， false 不删除存在表
     *
     * @var boolean
     */
    protected $DropTableExists = true;

    /**
     * 备份配置
     *
     * @var array
     */
    protected $config = [
        //数据库备份路径
        'path' => '',
        //数据库备份卷大小
        'part' => 20000, //20971520,
        //数据库备份文件是否启用压缩 0不压缩 1 压缩
        'compress' => 0
    ];

    /**
     * 不备份的表
     *
     * @var array
     */
    protected $noBackupTables = ['qs_sys_backup'];

    /**
     * 备份数据一次取多少条
     *
     * @var integer
     */
    protected $DataNum = 10;

    protected $FolderName = '';

    protected $FolderPath = '';

    protected $BasePath = '';

    protected $FileNamePrefix = 'data_backup_';

    protected $lockFileName = 'backup.lock';

    protected $error = '';

    /**
     * 记录sql语句条数
     *
     * @var integer
     */
    protected $sqlNum = 0;

    /**
     * 生成的SQL语句
     *
     * @var string
     */
    protected $sqlStr = '';

    protected $sqlSize = 0;

    protected $fieldAttr = [];

    protected static $instance;

    public function __construct(array $conf = [], string $folderName = '')
    {
        if (!empty($conf)) $this->config = $conf;

        $this->FolderName = empty($folderName) ? date('Ymd_His') : $folderName;

        $this->BasePath = base_path() . DIRECTORY_SEPARATOR . (empty($this->config['path']) ? 'backup_databases' : $this->config['path']) . DIRECTORY_SEPARATOR;

        $this->FolderPath = $this->BasePath . $this->FolderName . DIRECTORY_SEPARATOR;

        if (!empty($folderName)) $this->delDir($this->BasePath . $folderName);
    }

    /**
     * 设置不备份的数据表
     *
     * @param array $arr
     * @return void
     */
    public function setNoBackupTables($arr = []){
        $this->noBackupTables = $arr;
        return $this;
    }

    public static function instance(){
        if(!self::$instance) self::$instance = new static;

        return self::$instance;
    }


    public function getBasePath($folder = ''){
        return $this->BasePath.($folder ? $folder:'');
    }
    /**
     * 设置备份数据表
     *
     * @param array $tables
     * @return $this
     */
    public function setTables(array $tables)
    {
        $this->tables = $tables;
        return $this;
    }
    /**
     * Undocumented function
     *
     * @param integer $size 
     * @return $this
     */
    public function setRollSize(int $size)
    {
        $this->config['part'] = $size;
        return $this;
    }
    /**
     * 设置脚本运行超时时间
     * 0表示不限制，支持连贯操作
     */
    public function setTimeout($time = null)
    {
        if (!is_null($time)) {
            set_time_limit($time) || ini_set("max_execution_time", $time);
        }
        return $this;
    }

    /**
     * 设置数据库连接标识
     *
     * @param string $name
     * @return void
     */
    public function setDbConnect(string $name)
    {
        self::$dbConnectName = $name;
        return $this;
    }

    public function getBackupFolderName(){
        return $this->FolderName;
    }

    public function getDatabasesSize(){
        if(!$this->Db) $this->Db = self::connect();
        $dbName = env('DB_NAME');

        $info = $this->Db->query('SELECT table_schema AS "database",SUM(data_length + index_length) / 1024 / 1024 AS "size" FROM information_schema.TABLES WHERE table_schema = "'.$dbName.'"');
        // print_r($info[0]['size']);
        return round($info[0]['size'],2);
    }

    /**
     * 执行备份 转储到文件
     * @return mixed false 或 文件存储地址
     */
    public function dump()
    {

        $lock = $this->BasePath . $this->lockFileName;
        if (file_exists($lock)) return false;

        /*链接数据库*/
        if(!$this->Db) $this->Db = self::connect();

        $this->checkPath($this->FolderPath);
        $this->setLock();

        /*如果未指定数据表 则指定全部数据表*/
        if (0 == count($this->tables)) {
            $sth = $this->Db->query('SHOW TABLE STATUS');
            $list = array_map('array_change_key_case', $sth);

            foreach ($list as $row) {
                $this->tables[] = $row['name'];
            }
        }

        // print_r($this->tables);

        $this->setLock(["require" => $this->tables]);

        $this->writeInfo();
        /*sql按表输出到文件*/
        foreach ($this->tables as $table) {

            if(in_array($table, $this->noBackupTables)) continue;

            $this->setLock(["current" => $table]);

            $fieldAttr = $this->Db->query('SHOW COLUMNS FROM '.$table);
            $tmp = [];
            foreach($fieldAttr as $info) $tmp[$info['Field']] = $info;
            $this->fieldAttr = $tmp;
            
            $this->ouputToFile($table);
            $sqlSize =  strlen($this->sqlStr);

            if ($sqlSize > $this->config['part']) {
                file_put_contents($this->getOutFilePath(), $this->sqlStr, FILE_APPEND);

                $this->sqlSize += $sqlSize;

                $this->sqlStr = '';
                //换卷
                $this->roll += 1;
            }
            $this->setLock(["complete" => $table]);
        }


        file_put_contents($this->getOutFilePath(), $this->sqlStr, FILE_APPEND);

        // file_put_contents($this->FolderPath.'sql_num.info', $this->sqlNum);

        $this->sqlSize += strlen($this->sqlStr);

        $this->setLock('del');

        $size = round($this->sqlSize / 1024, 2);

        return ["size" => $size, "folder" => $this->FolderName, "roll" => $this->roll, "sql_num" => $this->sqlNum];
    }

    /**
     * 还原数据库
     *
     * @param integer $id
     * @return void
     */
    public function restore(int $id)
    {
        /*链接数据库*/
        if(!$this->Db) $this->Db = self::connect();
        $backupInfo = $this->Db->name("sys_backup")->find($id);

        $restoreInfo = $this->BasePath . 'restore.lock';
        if (file_exists($restoreInfo)) {
            $this->error = '数据库正在还原中，请不要重复操作！';
            return false;
        }

        if (empty($backupInfo)) {
            $this->error = '备份文件不存在！';
            return false;
        }

        $path = $this->BasePath . $backupInfo['folder'];

        $sqlNum = intval($backupInfo['sql_num']);//intval(file_get_contents($path.DIRECTORY_SEPARATOR.'sql_num.info'));
        file_put_contents($restoreInfo, $sqlNum . "|0");

        
        $files = $this->getFileLists($path, true);

        //计数器
        $i = 0;
        foreach ($files as $file) {
            $sqls = $this->getSqls($file);
            foreach ($sqls as $sql) {
                $i++;
                $e = $this->Db->execute($sql);
                if (false !== $e) {
                    file_put_contents($restoreInfo, $sqlNum . "|" . $i);
                } else {
                    $this->error = '还原失败！';
                    return false;
                }
            }
        }

        if (file_exists($restoreInfo)) @unlink($restoreInfo);

        return [$sqlNum, $i];
    }

    public function getRestoreStatus(){
        $restoreInfo = $this->BasePath . 'restore.lock';
        if (!file_exists($restoreInfo)) return false;
        $str = file_get_contents($restoreInfo);
        $arr = explode('|',$str);
        $res = $str ? ['t'=>intval($arr[0]), 'c'=>intval($arr[1])]:false;
        return $res;
    }

    protected function getSqls($file)
    {
        if (!file_exists($file)) {
            $this->error = 'sql 文件不存在！';
            return false;
        }
        $sql = file_get_contents($file);

        // $sql = str_replace("\r", "\n", $sql);
        $sql = trim($sql);

        $sql_arr = explode(";\n", $sql);

        $sql_arr = str_replace(["\;"], [";"], $sql_arr);

        return $sql_arr;
    }

    protected function getFileLists(string $folderPath, bool $return_all_path = false)
    {
        if (!is_dir($folderPath)) return false;

        $files = scandir($folderPath, SCANDIR_SORT_NONE);

        $res = [];

        foreach ($files as $file) {
            // 忽略当前目录和上级目录
            if ($file == '.' || $file == '..' || $file == 'sql_num.info') {
                continue;
            }
            $res[] = $return_all_path ? $folderPath . DIRECTORY_SEPARATOR . $file : $file;
        }

        return $res;
    }

    public function isLock()
    {
        $lockFilePath = $this->BasePath . $this->lockFileName;
        return file_exists($lockFilePath) ? true : false;
    }
    /**
     * 获取备份锁数据
     *
     * @return array
     */
    public function getLockData()
    {
        $lockFilePath = $this->BasePath . $this->lockFileName;
        
        if(!file_exists($lockFilePath)) return false;
        $lockDataStr =  file_get_contents($lockFilePath);//Redis::get('backupStatusInfo');
        
        $lockData = json_decode($lockDataStr, true);

        return $lockData;
    }

    public function getError()
    {
        return $this->error;
    }

    /**
     * 备份锁文件操作
     *
     * @param string $data
     * @return void
     */
    protected function setLock($data = '')
    {
        $lockFilePath = $this->BasePath . $this->lockFileName;
        if (empty($data)) {
            //已完成的备份表， 当前备份表， 请求备份的表
            $arr = ["complete" => [], "current" => "", "require" => []];
            file_put_contents($lockFilePath, json_encode($arr));
            
        } else if (is_array($data)) {
            $lockDataStr = file_get_contents($lockFilePath);
            $lockData = json_decode($lockDataStr, true);

            if (isset($data['complete'])) $lockData['complete'][] = $data['complete'];
            if (isset($data['current'])) $lockData['current'] = $data['current'];
            if (isset($data['require'])) $lockData['require'] = array_merge($data['require'], $lockData['require']);

            file_put_contents($lockFilePath, json_encode($lockData));

        } else if ($data == 'del') {
            @unlink($lockFilePath);
        }

        return true;
    }

    /**
     * 写入头部信息
     *
     * @return void
     */
    protected function writeInfo()
    {
        $info = "-- --------------------------------------------------\n";
        $info .= "-- 数据库备份\n";
        $info .= "--\n";
        $info .= "-- 日期：" . date("Y-m-d H:i:s") . "\n";
        $info .= "--           \n";
        $info .= "--           \n";
        file_put_contents($this->getOutFilePath(), $info, FILE_APPEND);
    }

    /**
     * sql按表输出到文件
     * @param $table
     * @param $file
     */
    protected function ouputToFile($table)
    {
        // $this->sqlStr = '';
        // print_r($table . "\n");

        /*头注释*/
        $this->sqlStr .= "-- --------------------------------------------------\n";
        $this->sqlStr .= "--          数据表 " . $table . "\n";
        $this->sqlStr .= "-- --------------------------------------------------\n\n";

        /*是否删除已存在的数据表*/
        if (true === $this->DropTableExists) {
            $this->sqlStr .= 'DROP TABLE IF EXISTS `' . $table . '`;';
            $this->sqlStr .= "\n\n";
            $this->sqlNum++;
        }

        /*获取建表语句*/
        $sth = $this->Db->query('SHOW CREATE TABLE `' . $table . '`');
        $create_sql = $sth[0];

        /*表存在就不再新建表*/
        $this->sqlStr .= preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_sql['Create Table']);
        $this->sqlStr .= ";\n\n";
        $this->sqlNum++;

        /*是否导出数据*/
        if ($this->withData === true) {
            $result = $this->Db->query("SELECT COUNT(*) AS count FROM `{$table}`");
            $count = $result['0']['count'];
            if ($count) $this->getTableData($table);
        }
        $this->sqlStr .= "\n\n";
    }

    protected function getTableData(string $table, int $start = 0)
    {
        $sth = $this->Db->query("SELECT * FROM `{$table}` LIMIT {$start}, {$this->DataNum}");
        foreach ($sth as $row) {
            $fields = [];
            foreach($row as $field=>$v){
                $tmp = addslashes($v);
                if(empty($v)) $tmp = $this->fieldDefauthVal($field);
                $fields[] = $tmp;
            }
            // print_r($fields);
            // $row = array_map('addslashes', $row);
            $val = str_replace(["\r", "\n", ";"], ["\\n", "\\n", "\;"], implode("', '", $fields));
            $this->sqlStr .= "INSERT INTO `{$table}` VALUES('" . $val . "');\n";
            $this->sqlNum++;
        }

        // $return_sql = $this->sqlStr.$return_sql;
        $len = strlen($this->sqlStr);


        //数据过大分卷
        if ($len > $this->config['part']) {
            // print_r('==sql长度：' . $len / 1024 . ' 数据条数：' . count($sth));
            //写入文件
            file_put_contents($this->getOutFilePath(), $this->sqlStr, FILE_APPEND);

            $this->sqlSize += $len;

            $this->sqlStr = '';
            //分卷
            $this->roll += 1;
        }

        //未取齐数据继续递归获取
        if (count($sth) == $this->DataNum) $this->getTableData($table, $start + $this->DataNum);
    }

    protected function fieldDefauthVal($field){
        $type = explode("(",$this->fieldAttr[$field]['Type']);
        $defVal = '';
        switch($type[0]){
            case 'int':
            case 'tinyint':
                $defVal = 0;
                break;
            case 'json':
                $defVal = 'null';
                break;
        }

        return $defVal;
    }
    /**
     * 设置文件名为默认值
     */
    protected function getOutFilePath()
    {
        return $this->FolderPath . $this->FileNamePrefix . $this->rollNum($this->roll) . '.' . $this->ext;
    }

    protected function rollNum($roll)
    {
        $num = '00000000';
        if ($this->roll == 0) return $num;
        $len = strlen($roll);
        $tmp = substr($num, 0, strlen($num) - $len);
        return $tmp . $roll;
    }
    /**
     * 数据库连接
     *
     * @return $obj
     */
    protected static function connect()
    {
        return Db::connect(self::$dbConnectName);
    }

    /**
     * 检查目录是否可写
     * @param  string   $path    目录
     * @return boolean
     */
    protected function checkPath($path)
    {
        if (is_dir($path)) {
            return true;
        }
        if (mkdir($path, 0755, true)) {
            return true;
        } else {
            return false;
        }
    }

    protected function delDir($dir)
    {
        if (!file_exists($dir)) return false;

        if (is_dir($dir)) {
            $files = scandir($dir);
            foreach ($files as $file) {
                if ($file != '.' && $file != '..') $this->delDir($dir . '/' . $file);
            }
            rmdir($dir);
        } else {
            unlink($dir);
        }
    }

    public function __destruct()
    {
        $this->sqlStr = '';
        $this->roll = 0;
        $this->sqlSize = 0;
        $this->sqlNum = 0;
    }
}
